package com.b511.swpu.cementbackend.repository;

import com.b511.swpu.cementbackend.util.DBHelper;
import net.sf.json.JSONObject;


public class EmployeeManageRepository {
    public JSONObject findEmployeeManage(int num,int size) throws Exception {
        DBHelper dbHelper=new DBHelper();
        String sql="select e1.NAME NAME,e1.EMPGROUP EMPGROUP,e1.POSITION POSITION,c1.NAME COMPANYNAME,u1.UNAME UNAME,r1.NAME ROLENAME"
                + " from SYSM_Employee e1" +
                " left join SYSM_Company c1" +
                " on e1.COMPANYCODE=c1.COMPANYCODE" +
                " left join SYSM_user u1" +
                " on e1.EMPLOYEEID=u1.EMPLOYEEID" +
                " left join SYSM_user_role ur1" +
                " on u1.UNAME=ur1.UNAME" +
                " left join SYSM_roles r1" +
                " on ur1.ROLECODE=r1.ROLECODE";
        String totalSql="select count(*) " +
                "from SYSM_Employee e1" +
                " left join SYSM_Company c1" +
                " on e1.COMPANYCODE=c1.COMPANYCODE" +
                " left join SYSM_user u1" +
                " on e1.EMPLOYEEID=u1.EMPLOYEEID" +
                " left join SYSM_user_role ur1" +
                " on u1.UNAME=ur1.UNAME" +
                " left join SYSM_roles r1" +
                " on ur1.ROLECODE=r1.ROLECODE";
        //数据库中总的记录数
        int total=dbHelper.executeSingleQuery(totalSql,null);
        int pagesize=size;
        int page=num;

        String data=dbHelper.executeQueryJsonByPage(sql,null,pagesize,page);
        JSONObject jsonobj = new JSONObject();
        jsonobj.put("page",page);
        jsonobj.put("total",total);
        jsonobj.put("list",data);
        return jsonobj;
    }


}
